# # coding=utf-8
import time
import string
import random
import xlrd
#最大行数65536
from xlutils.copy import copy

excel_path = '../..\项目文件\库存.xlsx'
data = xlrd.open_workbook(excel_path)
new_1 = copy(data)

start_time = time.strftime('%Y%m%d%H%M%S')
print("开始时间：" + str(start_time))

# 查看工作表
data.sheet_names()
print("全部sheet页如下：\n" + str(data.sheet_names()))
for s in data.sheet_names():
    # print(s)
    table = data.sheet_by_name(s)

    print("总行数：" + str(table.nrows))
    print("总列数：" + str(table.ncols))

    wr_sheet = new_1.get_sheet(s)
    i = 0
    j = 4021113014471000120

    gm1 = salt = ''.join(random.sample(string.ascii_letters + string.digits, 8))
    name = '商品'
    gc = '0804503000'
    dcl_unit = '007'
    ma = 'I'
    del_num = string.digits
    total = ''.join(random.sample(string.digits, 8))
    bz = '110'
    ck = ''.join(random.sample(string.digits, 5))
    kw = ''.join(random.sample(string.ascii_letters + string.digits, 4))

    m = 10
    for i in range(0, m):
        try:
            wr_sheet.write(i + 5, 0, str("%s" % ''.join(random.sample(string.ascii_letters + string.digits, 8))))
            wr_sheet.write(i + 5, 1, str("%s" % name))
            wr_sheet.write(i + 5, 2, str("%s" % gc))
            wr_sheet.write(i + 5, 3, str("%s" % dcl_unit))
            wr_sheet.write(i + 5, 4, str("%s" % ma))
            wr_sheet.write(i + 5, 5, str("%s" % ''.join(random.sample(string.digits, 5))))
            wr_sheet.write(i + 5, 6, str("%s" % ''.join(random.sample(string.digits, 8))))
            wr_sheet.write(i + 5, 7, str("%s" % bz))
            wr_sheet.write(i + 5, 8, str("%s" % ''.join(random.sample(string.digits, 5))))
            wr_sheet.write(i + 5, 9, str("%s" % ''.join(random.sample(string.ascii_letters + string.digits, 4))))
            print("写入。。。。%s" % j)
        except Exception as E:
            new_1.save(excel_path)
            print("%s写入失败：%S" % str(i), E)
        i+=1
        j+=1
    wr_sheet.write(m + 5, 0, str("END#"))
new_1.save(excel_path)  # 更新测试用例文件